﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;


//class created by Nguyen Van Phuc
//class chứa các hàm xử lí lập trình theo hướng đối tượng
namespace QLDonDatHang
{
    
    class ConnectToSQLSever
    {

        public SqlConnection database = new SqlConnection(@"Data Source=NGUYENVANPHUC\SQLEXPRESS;Initial Catalog=QL_BANHANG_GIOCHA;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        //public void taoKetNoiToDatabase(string diachi= "Data Source= NGUYENVANPHUC/SQLEXPRESS; Initial Catalog = QL_BANHANG_GIOCHA; Integrated Security = True; Connect Timeout = 15; Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False")
        //{
        //    database = new SqlConnection(@diachi);
        //    //chuoi ket noi csdl
        //}

        //---------------------------
        //Main
        //---------------------------

        //Hàm kiểm tra tài khoản và mật khẩu trong cơ sở dữ liệu
        public Boolean checkTaiKhoan(string taikhoan, string matkhau)
        {
            database.Open();
            SqlDataAdapter temp = new SqlDataAdapter("Select * From QL_TAIKHOAN", database);
            DataTable tb = new DataTable();
            temp.Fill(tb);
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                if (tb.Rows[i][0].ToString().Trim().Equals(taikhoan) && tb.Rows[i][1].ToString().Trim().Equals(matkhau))
                {
                    database.Close();
                    return true;
                }
            }
            database.Close();
            return false;
        }

        //Hàm kiểm tra sự tồn tại của tài khoản trong csdl
        public Boolean checkTaiKhoan(string taikhoan)
        {
            database.Open();
            SqlDataAdapter temp = new SqlDataAdapter("Select * From QL_TAIKHOAN",database);
            DataTable tb = new DataTable();
            temp.Fill(tb);
            for(int i = 0; i < tb.Rows.Count; i++)
            {
                if (tb.Rows[i][0].ToString().Trim().Equals(taikhoan))
                {
                database.Close();
                return true;
                }
            }
            database.Close();
            return false;
        }

        //Hàm insert thông tin tài khoản vào database
        public void insertTaiKhoanToDatabase(string taikhoan,string matkhau,string sdt,string email)
        {
            string truyVanSql = "insert into QL_TAIKHOAN(USES_NAME,USES_PASSWORD,USES_SDT,USES_EMAIL) VALUES('" + taikhoan+"','"+matkhau+"','"+sdt+"','"+email+"')";
            database.Open();
            SqlCommand sql = new SqlCommand(truyVanSql, database);
            sql.ExecuteNonQuery();
            database.Close();
        }
        // thêm đơn hàng
        public void insertDonDatHang(string IDDonHang, string TenDonHang,string SoLuong,string NgayDatHang,string NgayGiaoHang, string TenKhachHang, string TenMatHang)
        {
            string truyVanSql = "insert into QL_DONDATHANG(ID_DONHANG,TEN_DONHANG,SOLUONG,NGAYDATHANG,NGAYGIAOHANG,TEN_KHACHHANG,TEN_MATHANG) VALUES('" + IDDonHang + "','" + TenDonHang + "','" + SoLuong + "','" + NgayDatHang + "','" + NgayGiaoHang + "','" + TenKhachHang + "','" + TenMatHang + "')";
            database.Open();
            SqlCommand sql = new SqlCommand(truyVanSql, database);
            sql.ExecuteNonQuery();
            database.Close();
        }
        // kiểm tra tên khách hàng
        public  Boolean checkTenKhachhang(string Tenkhachhang)
        {
            database.Open();
            SqlDataAdapter temp = new SqlDataAdapter("Select * From QL_KHACHHANG",database);
            DataTable tb = new DataTable();
            temp.Fill(tb);
            for(int i = 0; i < tb.Rows.Count; i++)
            {
                if (tb.Rows[i][0].ToString().Equals(Tenkhachhang))
                {
                database.Close();
                return true;
                }
            }
            database.Close();
            return false;
        }
        //kiểm tra mã hàng
        public Boolean checkTenMatHang(string Tenhang)
        {
            database.Open();
            SqlDataAdapter temp = new SqlDataAdapter("Select * From QL_MATHANG", database);
            DataTable tb = new DataTable();
            temp.Fill(tb);
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                if (tb.Rows[i][0].ToString().Equals(Tenhang))
                {
                    database.Close();
                    return true;
                }
            }
            database.Close();
            return false;
        }
        // kiểm tra ID đơn Hàng
        public Boolean checkIDDonHang(string IDDonHang)
        {
            database.Open();
            SqlDataAdapter temp = new SqlDataAdapter("Select * From QL_DONDATHANG", database);
            DataTable tb = new DataTable();
            temp.Fill(tb);
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                if (tb.Rows[i][0].ToString().Equals(IDDonHang))
                {
                    database.Close();
                    return true;
                }
            }
            database.Close();
            return false;
        }
      
        //Hàm xóa tất cả textbox trong form
        public void clearTextBox(Form tenForm)
        {
            foreach (Control ctrl in tenForm.Controls)
            {
                if (ctrl is TextBox) ((TextBox)ctrl).Text = "";
            }
        }

        //
        public void addMatHang(string tenmathang,string donvitinh,string dongia)
        {
            string truyVanSql = "insert into QL_MATHANG(TEN_MATHANG,DONVITINH,DONGIA) VALUES('" + tenmathang + "','" + donvitinh + "','" + dongia+ "')";
            database.Open();
            SqlCommand sql = new SqlCommand(truyVanSql, database);
            sql.ExecuteNonQuery();
            database.Close();
        }
    }
}
